<?php
/**
 * isExistTable()
 *
 * @param string $table_name
 * @return
 */
function isExistTable($table_name = "")
{
    $sql = "SHOW TABLES";
    $result = zeus_sql_query($sql, false, 'All');
    if (isset($result)) {
        $key = "Tables_in_" . DB_NAME;
        foreach($result AS $tablename) {
            $table[] = $tablename[$key];
        }
        if (in_array(DB_PREFIX . $table_name, $table)) {
            $sql = "SELECT COUNT(*) FROM " . DB_PREFIX . $table_name;
            $result = zeus_sql_query($sql, false);
            if (0 != $result['COUNT(*)']) {
                return true;
            } else {
                return false;
            }
        } else {
            return false;
        }
    } else {
        return false;
    }
}

/**
 * createUserGridView()
 *
 * @return
 */
function createUserGridHeap()
{
    // Heap
    $create_heap_sql = "DROP TABLE IF EXISTS " . DB_PREFIX . "user_grid_view;

/*==============================================================*/
/* Table: sv_user_grid_view                                     */
/*==============================================================*/
CREATE TABLE IF NOT EXISTS " . DB_PREFIX . "user_grid_view
(
   user_id                        varchar(20)                    not null,
   user_name                      varchar(50),
   real_name                      varchar(50),
   sex_id                         varchar(10),
   cell_phone                     varchar(50),
   email                          varchar(50),
   role_id                        varchar(10),
   last_login_time                varchar(50),
   signup_time                    varchar(50),
   is_enable                      varchar(10),
   primary key (user_id)
)TYPE=HEAP DEFAULT CHARSET UTF8
";
    zeus_sql_exec($create_heap_sql, false);
    return true;
}
/**
 * updateUserGridHeap()
 *
 * @return
 */
function updateUserGridHeap()
{
    // Get info
    $get_info_sql = "SELECT " . DB_PREFIX . "user_auth.user_id," .
    DB_PREFIX . "user_auth.user_name," .
    DB_PREFIX . "user_auth.is_enable," .
    DB_PREFIX . "user_detail.sex_id," .
    DB_PREFIX . "user_detail.real_name," .
    DB_PREFIX . "user_detail.cell_phone," .
    DB_PREFIX . "user_auth.role_id," .
    DB_PREFIX . "user_detail.last_login_time," .
    DB_PREFIX . "user_detail.signup_time," .
    DB_PREFIX . "user_detail.email"
     . " FROM " .
    DB_PREFIX . "user_auth," .
    DB_PREFIX . "user_detail WHERE " .
    DB_PREFIX . "user_auth.user_id = " .
    DB_PREFIX . "user_detail.user_id";
    // Insert heap
    $insert_heap_sql = "REPLACE INTO " . DB_PREFIX . "user_grid_view SET
		user_id=:user_id,user_name=:user_name,real_name=:real_name,
		sex_id=:sex_id,cell_phone=:cell_phone,email=:email,role_id=:role_id,
		last_login_time=:last_login_time,signup_time=:signup_time,is_enable=:is_enable
		";
    $result = zeus_sql_query($get_info_sql, false, 'All');
    if (isset($result)) {
        foreach($result AS $info) {
            $aInfo[] = array(":user_id" => $info['user_id'],
                ":user_name" => $info['user_name'],
                ":real_name" => $info['real_name'],
                ":sex_id" => $info['sex_id'],
                ":cell_phone" => $info['cell_phone'],
                ":role_id" => $info['role_id'],
                ":last_login_time" => $info['last_login_time'],
                ":signup_time" => $info['signup_time'],
                ":is_enable" => $info['is_enable'],
                ":email" => $info['email']
                );
        }
        zeus_sql_prepare($insert_heap_sql, false, $aInfo);
        return true;
    } else {
        return false;
    }
}

/**
 * emptyTable()
 *
 * @param string $sTableName
 * @return
 */
function emptyTable($sTableName = "")
{
    $sql = "DELETE FROM " . DB_PREFIX . $sTableName;
    if (zeus_sql_exec($sql, false)) {
        return true;
    } else {
        return false;
    }
}

/**
 * createRoleGridHeap()
 *
 * @return
 */
function createRoleGridHeap()
{
    // Heap
    $create_heap_sql = "drop table if exists " . DB_PREFIX . "role_grid_view;
/*==============================================================*/
/* Table: sv_role_grid_view                                     */
/*==============================================================*/
create table if not exists " . DB_PREFIX . "role_grid_view
(
   role_id                        varchar(20)                    not null,
   role_name                      varchar(50),
   description                    varchar(200),
   member_num                     varchar(50),
   primary key (role_id)
)TYPE=HEAP DEFAULT CHARSET UTF8
";
    // Get info
    $get_info_sql = "SELECT " . DB_PREFIX . "role.role_id," .
    DB_PREFIX . "role.role_name," .
    DB_PREFIX . "role.description,COUNT(" .
    DB_PREFIX . "user_auth.user_id)" . " FROM " .
    DB_PREFIX . "user_auth," .
    DB_PREFIX . "role WHERE " .
    DB_PREFIX . "user_auth.role_id = " .
    DB_PREFIX . "role.role_id " . "GROUP BY " .
    DB_PREFIX . "role.role_id";
    // Insert heap
    $insert_heap_sql = "REPLACE INTO " . DB_PREFIX . "role_grid_view SET
    role_id = :role_id, role_name = :role_name, description = :description,
    member_num = :member_num";
    if (isExistTable("role_grid_view")) {
        return true;
    } else {
        $member_num = "COUNT(" . DB_PREFIX . "user_auth.user_id)";
        $result = zeus_sql_query($get_info_sql, false, 'All');
        if (isset($result)) {
            foreach($result AS $info) {
                $aInfo[] = array(":role_id" => $info['role_id'],
                    ":role_name" => $info['role_name'],
                    ":description" => $info['description'],
                    ":member_num" => $info[$member_num]
                    );
            }
            zeus_sql_exec($create_heap_sql, false);
            zeus_sql_prepare($insert_heap_sql, false, $aInfo);
        } else {
            return false;
        }
    }
}

/**
 * createClassHeap()
 *
 * @return
 */
function createClassGridHeap()
{
    $currentYear = date('Y');
    // Heap
    $create_heap_sql = "drop table if exists " . DB_PREFIX . "class_grid_view;
/*==============================================================*/
/* Table: sv_class_grid_view                                     */
/*==============================================================*/
create table if not exists " . DB_PREFIX . "class_grid_view
(
   myclass_id                        varchar(20)                    not null,
   myclass_name                      varchar(50),
   master_id                      varchar(50),
   description                    varchar(200),
   is_enable                      varchar(20),
   graduate_time				  varchar(50),
   member_num                     varchar(50),
   primary key (myclass_id)
)TYPE=HEAP DEFAULT CHARSET UTF8
";
    // Get info
    $get_info_sql = "SELECT " . DB_PREFIX . "myclass.myclass_id," .
    DB_PREFIX . "myclass.myclass_name," .
    DB_PREFIX . "myclass.master_id," .
    DB_PREFIX . "myclass.is_enable," .
    DB_PREFIX . "myclass.graduate_time," .
    DB_PREFIX . "myclass.description,COUNT(" .
    DB_PREFIX . "rel_student_info.student_no)" . " FROM " .
    DB_PREFIX . "rel_student_info," .
    DB_PREFIX . "myclass WHERE CONCAT('" . $currentYear . ":'," .
    DB_PREFIX . "myclass.myclass_id) LIKE " .
    DB_PREFIX . "rel_student_info.myclass_id" . " GROUP BY " .
    DB_PREFIX . "myclass.myclass_id";
    // Insert heap
    $insert_heap_sql = "REPLACE INTO " . DB_PREFIX . "class_grid_view SET
    graduate_time=:graduate_time,myclass_id  = :myclass_id ,is_enable  = :is_enable , myclass_name = :myclass_name, description = :description,
    member_num = :member_num,master_id=:master_id";
    if (isExistTable("class_grid_view")) {
        return true;
    } else {
        $member_num = "COUNT(" . DB_PREFIX . "rel_student_info.student_no)";
        $result = zeus_sql_query($get_info_sql, false, 'All');
        if (isset($result)) {
            foreach($result AS $info) {
                $aInfo[] = array(":myclass_id" => $info['myclass_id'],
                    ":myclass_name" => $info['myclass_name'],
                    ":description" => $info['description'],
                    ":master_id" => $info['master_id'],
                    ":is_enable" => $info['is_enable'],
                    ":member_num" => $info[$member_num],
                    ":graduate_time" => $info['graduate_time']
                    );
            }
            zeus_sql_exec($create_heap_sql, false);
            zeus_sql_prepare($insert_heap_sql, false, $aInfo);
        } else {
            return false;
        }
    }
}

/**
 * createStudentHeap()
 *
 * @return
 */

function createStudentGridHeap()
{
    $create_heap_sql = "drop table if exists " . DB_PREFIX . "student_grid_view;

/*==============================================================*/
/* Table: sv_student_grid_view                                  */
/*==============================================================*/
create table if not exists " . DB_PREFIX . "student_grid_view
(
   student_id                     varchar(20)                    not null,
   student_no                     varchar(50),
   student_name                   varchar(50),
   sex_id                         varchar(20),
   myclass_id                     varchar(20),
   deparment_id                   varchar(20),
   ic_card_num                    varchar(50),
   cell_phone                     varchar(50),
   home_phone                    varchar(50),
   dorm_id                        varchar(20),
   is_enable                      varchar(20),
   signup_time                    datetime,
   primary key (student_id)
)TYPE=HEAP DEFAULT CHARSET UTF8
";
    zeus_sql_exec($create_heap_sql, false);
    return true;
}
/**
 * updateStudentGridHeap()
 *
 * @return
 */
function updateStudentGridHeap()
{
    // Get info
    $get_info_sql = "SELECT " . DB_PREFIX . "student.student_id," .
    DB_PREFIX . "student.student_no," .
    DB_PREFIX . "student.student_name," .
    DB_PREFIX . "rel_student_info.myclass_id," .
    DB_PREFIX . "rel_student_info.sex_id," .
    DB_PREFIX . "rel_student_info.deparment_id," .
    DB_PREFIX . "rel_student_info.dorm_id," .
    DB_PREFIX . "rel_student_info.is_enable," .
    DB_PREFIX . "rel_student_info.detail" . " FROM " .
    DB_PREFIX . "rel_student_info," .
    DB_PREFIX . "student WHERE " .
    DB_PREFIX . "student.student_no = " .
    DB_PREFIX . "rel_student_info.student_no" . " GROUP BY " .
    DB_PREFIX . "student.student_no";
    // Insert heap
    $insert_heap_sql = "REPLACE INTO " . DB_PREFIX . "student_grid_view SET
    student_id=:student_id,
    student_no=:student_no,
    student_name=:student_name,
    sex_id=:sex_id,
    myclass_id=:myclass_id,
    deparment_id=:deparment_id,
    ic_card_num=:ic_card_num,
    cell_phone=:cell_phone,
    home_phone =:home_phone ,
    dorm_id=:dorm_id,
    is_enable=:is_enable,
    signup_time=:signup_time";
    $result = zeus_sql_query($get_info_sql, false, 'All');
    // var_dump($result);
    if (isset($result)) {
        foreach($result AS $info) {
            $myclass_id = unserialize($info['myclass_id']);
            $termIndex = getTermIndex();
            $aInfo[] = array(":student_id" => $info['student_id'],
                ":student_name" => $info['student_name'],
                ":student_no" => $info['student_no'],
                ":sex_id" => $info['sex_id'],
                ":myclass_id" => $myclass_id,
                ":deparment_id" => $info['deparment_id'],
                ":ic_card_num" => $ic_card_num,
                ":cell_phone" => $cell_phone,
                ":home_phone" => $home_phone,
                ":dorm_id" => $info['dorm_id'],
                ":is_enable" => $info['is_enable'],
                ":signup_time" => $info['signup_time']
                );
        }
        zeus_sql_prepare($insert_heap_sql, false, $aInfo);
    } else {
        return false;
    }
}
/**
 * dropTable()
 *
 * @param string $sTableName
 * @return
 */
function dropTable($sTableName = "")
{
    if (isExistTable($sTableName)) {
        $sql = "drop table if exists " . DB_PREFIX . $sTableName;
        zeus_sql_exec($sql, false);
        return true;
    } else {
        return false;
    }
}

?>